package net.tngou.qcc;


import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;


import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import net.tngou.lucene.Field.Type;
import net.tngou.lucene.Field;
import net.tngou.lucene.TngouDBHelp;

public class ExcleUtil {

	 public static final String OFFICE_EXCEL_XLS = "xls";
	 public static final String OFFICE_EXCEL_XLSX = "xlsx";
	 public static TngouDBHelp tngou = new TngouDBHelp();
	 public static void   writeExcel(List<LinkedHashMap<String, String>> list,String  filepath,String keyname) {
		 LinkedHashMap<String, String> map = list.get(0);
		 Set<String> keys = map.keySet();
		 Object[] header = keys.toArray();
		 String suffiex = getSuffiex(filepath);
		 int lenght = header.length;
         Workbook workbook;
         if ("xls".equals(suffiex.toLowerCase())) {
             workbook = new HSSFWorkbook();
         } else {
             workbook = new XSSFWorkbook();
         }
		 Sheet sheet = workbook.createSheet();
		 String head[]= {"Creditcode","PhoneNumber","BankAccount","Bank","OperName"};
		
		 //头部
		 Row row = sheet.createRow(0);
		 for (int i = 0; i < lenght; i++) {
              Cell cell = row.createCell(i);    
              cell.setCellValue(header[i].toString());    
         }
		 for (int i = 0; i < head.length; i++) {
             Cell cell = row.createCell( lenght+i);    
             cell.setCellValue(head[i]);    
        }
		 int t=0;
		 int max=list.size();
		 for (int i = 1; i <=list.size(); i++) {
			 LinkedHashMap<String, String> m = list.get(i-1);
			 row = sheet.createRow(i);
			 for (int j = 0; j< lenght; j++) {
	              Cell cell = row.createCell(j);    
	              cell.setCellValue(m.get(header[j].toString()));    
	         }
			 if(m.get(keyname)!=null)
			 {
				 String name = m.get(keyname).toString().replaceAll("（", "(").replaceAll("）", ")").replaceAll(" ", "");
				 Field fild=new Field("Name",name, Type.Key);
				 Map<String, Object> mm = tngou.get("bill", fild);
				 if(mm!=null) {
					 for (int j = 0; j < head.length; j++) {
			             Cell cell = row.createCell( lenght+j);  
			            Object value = mm.get(head[j]);
			            if(value==null)value="";		            
			             cell.setCellValue(value.toString());    
			        }
				 }
			 }
			 t++;
			 if(t%100==0)
			 {
				 System.err.println("运行****************"+t+"/"+max+"("+t*100/max+"%)");
			 }
			 
		}
		 FileOutputStream outputStream = null;
	
             try {
				outputStream = new FileOutputStream(filepath);
				  workbook.write(outputStream);
				   outputStream.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

             try {
				workbook.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
          
         
		  
		 
	}
		 
	public static List<LinkedHashMap<String, String>> readExcel(String filepath) {	
		 String suffiex=getSuffiex(filepath);
		 if (OFFICE_EXCEL_XLS.equals(suffiex) || OFFICE_EXCEL_XLSX.equals(suffiex)) {   
			try {
				 FileInputStream fs = new FileInputStream(filepath);	
				  Workbook wb;
		            if ("xls".equals(suffiex.toLowerCase())) {
		            	wb = new HSSFWorkbook(fs);
		            } else {
		            	wb = new XSSFWorkbook(fs);
		            }
				
		         Sheet sheet = wb.getSheetAt(0);
		         int rows=sheet.getPhysicalNumberOfRows();
		         Row header = sheet.getRow(0);
		         int cellnum = header.getPhysicalNumberOfCells();
		         String [] keys= new String[cellnum];
			      for (int i = 0; i < cellnum ; i++) {
			    	  keys[i]=header.getCell(i).getStringCellValue();
			    	
				 }
		         List<LinkedHashMap<String, String>> list = new ArrayList<LinkedHashMap<String, String>>();
		         for (int i = 1; i < rows; i++) {
		        	 Row row = sheet.getRow(i);
		        	 LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
		        	 for (int j = 0; j < cellnum ; j++) {
		        		 if(row.getCell(j)!=null)
				    	   map.put(keys[j], row.getCell(j).getStringCellValue());
				    	  
					 }
		        	  list.add(map);
		        	 
				}
		         wb.close();
		         fs.close();
		         return list;
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			}
			 return null;      
		  
		 }
	 /**
	     * 获取后缀
	     * @param filepath filepath 文件全路径
	     */
	    private static String getSuffiex(String filepath) {
	      
	        int index = filepath.lastIndexOf(".");
	        if (index == -1) {
	            return "";
	        }
	        return filepath.substring(index + 1, filepath.length());
	    }

}
